package jmu.dao.impl;

import jmu.dao.StudentsDAO;
import jmu.db.ConnectionManager;
import jmu.vo.Students;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository
public class StudentsDAOImpl implements StudentsDAO {
    @Override
    public List<Students> findAllStudents() throws Exception {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Students> all = new ArrayList<Students>();
        try{
            con = ConnectionManager.getConnection();  //1.创建连接
            String strSql = "select * from students";
            pstmt = con.prepareStatement(strSql);  //2.创建预处理语句总管
            rs = pstmt.executeQuery(); //3.语句总管执行SQL语句
            Students stu = null;
            while(rs.next()){ //4.处理结果集
                stu = new Students();
                stu.setStudentId(rs.getString("studentId"));
                stu.setStudentName(rs.getString("studentName"));
                stu.setStudentPwd(rs.getString(3));
                stu.setGender(rs.getString(4));
                stu.setClassId(rs.getInt("classId"));
                all.add(stu);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            ConnectionManager.closeStatement(pstmt);
            ConnectionManager.closeConnection(con);
        }
        return all;
    }

    @Override
    public List<Students> getStudentsLike(String keywords) throws Exception {
        List<Students> likeStudents=new ArrayList<Students>();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = ConnectionManager.getConnection();
            String sql = "select * from students where studentId like ? or studentName like ? or sex like ?";
            pstmt  = con.prepareStatement(sql);
            pstmt.setString(1,"%"+keywords+"%");
            pstmt.setString(2,"%"+keywords+"%");
            pstmt.setString(3,"%"+keywords+"%");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String studentId = rs.getString(1);
                String studentName = rs.getString("studentName");
                String studentPwd = rs.getString(3);
                String gender = rs.getString("sex");
                Integer classId = rs.getInt(5);
                //构造方法组装成一个Students对象stu
                Students stu = new Students(studentId, studentName, studentPwd, gender, classId);
                likeStudents.add(stu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            ConnectionManager.closeResultSet(rs);//5.关闭结果集 对象、语句总管、连接
            ConnectionManager.closeStatement(pstmt);
            ConnectionManager.closeConnection(con);
        }
        return likeStudents;
    }

    @Override
    public Students findStudentsByStudentId(String id) throws Exception {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = ConnectionManager.getConnection();
            String sql = "select * from students where studentId = ?";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1,id);
            rs = pstmt.executeQuery();
            if(rs.next()){
                String studentId = rs.getString(1);
                String studentName = rs.getString(2);
                String studentPwd = rs.getString(3);
                String gender = rs.getString(4);
                Integer classId = rs.getInt(5);
                Students stu = new Students(studentId, studentName, studentPwd, gender, classId);
                return stu;
            }
        } catch (SQLException e){
            e.printStackTrace();
        } finally {
            ConnectionManager.closeResultSet(rs);
            ConnectionManager.closeStatement(pstmt);
            ConnectionManager.closeConnection(con);
        }
        return null;
    }

}
